/*
 * UWT 2014
 */
package database;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import controller.VisualCandoControls;

public final class CreateCompoundMappingTable {
	private static final Logger logger = LoggerFactory.getLogger(CreateCompoundMappingTable.class);
	
	//Static variables for compound mapping table
	/**
	 * Column one name.
	 */
	public static final String COLUMN_ONE_NAME = "compound_ID";
	/**
	 * Column one type.
	 */
	private static final String COLUMN_ONE_TYPE = "INTEGER NOT NULL";
	
	/**
	 * Column two name.
	 */
	public static final String COLUMN_TWO_NAME = "cando_number";
	/**
	 * Column two type.
	 */
	private static final String COLUMN_TWO_TYPE = "INTEGER NOT NULL";
	
	/**
	 * Column three name.
	 */
	public static final String COLUMN_THREE_NAME = "compound_name";
	/**
	 * Column three type.
	 */
	private static final String COLUMN_THREE_TYPE = "VARCHAR(360) NOT NULL";
	
	/**
	 * Compound mapping primary key.
	 */
	private static final String PRIMARY_KEY = COLUMN_ONE_NAME;
	
	/**
	 * Compound mapping table name.
	 */
	private final String tableName;
	
	/**
	 * Database connection.
	 */
	private final CandoDatabase db;
	
	/**
	 * Main to run CreateCandoTable object.
	 * @param args main arguments.
	 */
	public static void main(String[] args) {
		final CandoDatabase db = new CandoDatabase(new VisualCandoControls());
		final CreateCompoundMappingTable compoundTable = new CreateCompoundMappingTable(db);
		db.executeScript(compoundTable.getCompoundMappingScript());
		try {
			compoundTable.populateCompoundMappingTable(db.getDBConnection());
		} catch (SQLException e) {
			logger.error("SQL error populating compound mapping table: ", e);
			db.displayErrorMessage("SQL error while populating compound mapping table. Message: " + e.toString());
		} catch (IOException e) {
			logger.error("IO error populating compound mapping table: ", e);
			db.displayErrorMessage("IO error while populating compound mapping table. Message: " + e.toString());
		}
		
	}
	
	/**
	 * Constructs CreateCompoundMappingTable object.
	 * @param db Database connection.
	 */
	public CreateCompoundMappingTable(final CandoDatabase db) {
		this.db = db;
		this.tableName = this.db.getCompoundTableName();
	}
	
	/**
	 * Create and return script to create compound mapping table.
	 * 
	 * @return Returns script to create compound mapping table.
	 */
	public String getCompoundMappingScript() {
		logger.info("CREATING table: {}", this.tableName);
		final StringBuilder sb = new StringBuilder();
		sb.append("CREATE TABLE ");
		sb.append("`" + this.db.getSchema() + "`");
		sb.append(".");
		sb.append("`" + this.tableName + "`");
		sb.append(" (");
		sb.append("`" + COLUMN_ONE_NAME + "` " + COLUMN_ONE_TYPE + ", ");
		sb.append("`" + COLUMN_TWO_NAME + "` " + COLUMN_TWO_TYPE + ", ");
		sb.append("`" + COLUMN_THREE_NAME + "` " + COLUMN_THREE_TYPE + ", ");
		sb.append("PRIMARY KEY " + "(`" + PRIMARY_KEY + "`));");
		
		return sb.toString();
	}
	
	/**
	 * Populates compound mapping table.
	 * 
	 * @param conn SQL Connection.
	 * @throws SQLException Exception thrown from SQL error.
	 * @throws IOException IO error.
	 */
	public void populateCompoundMappingTable(final Connection conn) throws SQLException, IOException {
		logger.info("Initiating population of compound mapping table...");
		final StringBuilder sb = new StringBuilder();
		sb.append("INSERT INTO ");
		sb.append(this.tableName);
		sb.append("(`" + COLUMN_ONE_NAME + "`, `");
		sb.append(COLUMN_TWO_NAME + "`, `");
		sb.append(COLUMN_THREE_NAME + "`) ");
		sb.append("VALUES(?, ?, ?);");
		
		PreparedStatement insertPrepared = null;
		final String statement = sb.toString();
		try {			
			insertPrepared = conn.prepareStatement(statement);	
			logger.info("Prepared statement created successfully. Statement: {}", statement);
		} catch (SQLException e) {
			logger.error("Error creating prepared statement: {}. Message: {}", statement, e);
			this.db.displayErrorMessage("Error creating following SQL prepared statement: " + statement +
										"\n\nError Message: " + e.toString());
		}
		
		final BufferedReader bReader = new BufferedReader(new FileReader(this.db.getCompoundMappingFile()));		
		String line;
		conn.setAutoCommit(false);
		while ((line = bReader.readLine()) != null) {
			String values[] = line.split("\t");
			insertPrepared.setInt(1, Integer.parseInt(values[0]));
			insertPrepared.setInt(2, Integer.parseInt(values[1]));
			insertPrepared.setString(3, values[2]);
			insertPrepared.executeUpdate();
			
		}
		conn.commit();
		bReader.close();	
		logger.info("Population of compound mapping table complete.");
		
	}

}
